import os
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle
from openpyxl.styles import PatternFill, Border, Side, Alignment
from openpyxl.styles.colors import Color

#---------------------------------------------------------------
#orange border
line_3 = Side(style='thick', color='FACC2E')
border3 = Border(top=line_3, bottom=line_3, left=line_3, right=line_3)
# alignment
alignment = Alignment(horizontal='left', vertical='center')
# yellow
keep_sty = NamedStyle(name='keep_sty', fill=PatternFill('solid', fgColor='ffff00'), alignment=alignment)
#red
diff_sty = NamedStyle(name='diff_sty', fill=PatternFill('solid', fgColor='FF0033'), alignment=alignment)
#green
same_sty = NamedStyle(name='same_sty', fill=PatternFill('solid', fgColor='339933'), alignment=alignment)
# has blank space
has_blank_space_sty = NamedStyle(name='has_blank_space_sty',  border=border3, alignment=alignment)
#--------------------------------------------------------------


def load_dump_data(filename):
    dump1_f = open(filename, 'r')
    dump1_f_dict = {}
    k = ""
    v = ""
    for f in dump1_f:
        if ":" in f:
            k = f.split(':')[0].split('CMIS')[1].strip(' ')
            continue
        v = f.replace('\n','').split(' ')
        if k == '':
            continue
        dump1_f_dict.update({k: v})
    # print(dump1_f_dict)
    # for k,v in dump1_f_dict.items():
    #     print(k)
    #     print(v)
    #     print('----------------------------------------------------')
    return dump1_f_dict


def load_temp_data_and_copy_summary_sheet(filename):
    wb = load_workbook(filename, data_only=True, keep_vba=True)
    try:
        #if Sumarry Copy exist remove first!
        del wb["Summary Copy"]
    except:
        print("No Summary Copy sheet, create it now ...")
    # print(wb.sheetnames)
    sheet1 = wb["Summary"]

    try:
        print("Create a Summary Copy sheet, Ok.")
        target = wb.copy_worksheet(sheet1)
    except Exception as e:
        print(str(e))
    return wb


def showVersion():
    print("   ________  ____________    ______                                    ")
    print("  / ____/  |/  /  _/ ___/   / ____/___  ____ ___  ____  ____ _________ ")
    print(" / /   / /|_/ // / \__ \   / /   / __ \/ __ `__ \/ __ \/ __ `/ ___/ _ \\")
    print("/ /___/ /  / // / ___/ /  / /___/ /_/ / / / / / / /_/ / /_/ / /  /  __/")
    print("\____/_/  /_/___//____/   \____/\____/_/ /_/ /_/ .___/\__,_/_/   \___/ ")
    print("                                              /_/                      ")
    print("  ______            __")
    print(" /_  __/___  ____  / /")
    print("  / / / __ \/ __ \/ / ")
    print(" / / / /_/ / /_/ / /  ")
    print("/_/  \____/\____/_/   ")
    print("                      ")
    print('-------------------------------------------')
    print("CMIS Compare Tool")
    print("Version: 0.0.1")
    print("Email: kai.zeng@lumentum.com")
    print('-------------------------------------------')


def selectCompareFiles():
    docs = os.listdir()
    docs_dict = {}
    doc_idx = 1
    for doc in docs:
        if '.xlsx' in doc or '.xlsm' in doc or '.txt' in doc:
            docs_dict.update({doc_idx : doc})
            doc_idx = doc_idx + 1

    print(5*'---' + 'Current Files' + 5*'---')
    # print(docs_dict)
    for k,v in docs_dict.items():
        print("{}. {}".format(k, v))
    print('-------------------------------------------')

    template_file_idx = int(input("Please input Template file index: "))
    while template_file_idx not in docs_dict.keys():
        print('File NOT exist! Please select again.')
        template_file_idx = int(input("Please input Template file index: "))

    dump_file_idx = int(input("Please input Dump file index: "))
    while dump_file_idx not in docs_dict.keys():
        print('File NOT exist! Please select again.')
        dump_file_idx = int(input("Please input Dump file index: "))

    return (docs_dict[template_file_idx], docs_dict[dump_file_idx])



def startCompareFiles(template_file, dump_file):
    wb = load_temp_data_and_copy_summary_sheet(template_file)
    sheet = wb["Summary Copy"]

    max_row = sheet.max_row
    max_column = sheet.max_column
    # print("row1:{} cols:{}".format(max_row, max_column))

    #load dump data
    dump_data_dict = load_dump_data(dump_file)

    # compare lower data, Lower Page
    print("Start compare Lower page data ...")
    for col in sheet.iter_cols(min_col=2, max_col=2, min_row=3, max_row=max_row-1):
        v = []
        cell_idx = 0
        for cell in col:
            # print(cell.value)
            cell_idx = cell_idx + 1
            if cell_idx == 1:
                continue

            if len(str(cell.value)) == 1:
                cell_val = '0' + str(cell.value)
            else:
                cell_val = str(cell.value)
                
            print("Lower Page {}h/{}  Template Data:{} <---> Dump Data:{}".format( 0, cell_idx-2, cell_val,  dump_data_dict["Lower Page"][cell_idx-2]))
            if cell_val == dump_data_dict["Lower Page"][cell_idx-2]:
                cell.style = same_sty
            else:
                cell.style = diff_sty
                cell.value = cell_val + '/' + dump_data_dict["Lower Page"][cell_idx-2]

    print("Start compare Upper page data ...")
    for col in sheet.iter_cols(min_col=4, max_col=max_column, min_row=3, max_row=max_row-1):
        dump_val = []
        cell_idx = 0
        PAGE_IN_DUMP = True
        p = ''
        #here only load dump upper data to list dump_val, 
        for cell in col:
            if len(str(cell.value)) == 1:
                p = '0' + str(cell.value)
            else:
                p = str(cell.value)

            k = 'Upper Page '+ p + 'h'
            # temp_summary_dict.update({k : v[1:]})
            if k in dump_data_dict.keys():
                dump_val = dump_data_dict[k]
                PAGE_IN_DUMP = True
            else:
                PAGE_IN_DUMP = False
            print('Load dump Upper data, ok')
            break

        #here start compare data
        for cell in col:
            cell_idx = cell_idx + 1
            if cell_idx == 1:
                continue

            if PAGE_IN_DUMP:
                if len(str(cell.value)) == 1:
                    cell_val = '0' + str(cell.value)
                else:
                    cell_val = str(cell.value)

                print("Upper Page {}h/{} Template Data:{} <---> Dump Data:{}".format(p, 128 + (cell_idx-2), cell_val,  dump_val[cell_idx-2]))
                if cell_val == dump_val[cell_idx-2]:
                    cell.style = same_sty
                else:
                    cell.style = diff_sty
                    cell.value = cell_val + '/' + dump_val[cell_idx-2]
            else:
                print("Not Found in Dump file, Keep color...")

    wb.move_sheet('Summary Copy', -1000)

    outfile = template_file.split('.')[0] + '__&__' + dump_file.split('.')[0] + '.xlsm'
    wb.save(filename=outfile)
    return outfile


if __name__ == '__main__':
    showVersion()
    template_file, dump_file = selectCompareFiles()

    print("You select Template file: {}, Dump file: {}".format(template_file, dump_file))
    outfile = startCompareFiles(template_file, dump_file)

    print("All done. A new sheet 'Summary Copy' has been generated in " + outfile + "! Please Check.")
    print('')
    print("Cell Color Meaning")
    print("Red:    Diff")
    print("Green:  Same")
    print("Yellow: Uncheck")

    close_win = input("Press any key to quit ...")

